package Q;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class EX01_SungJukDAO implements EX01_ISungJukDAO {

	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:dlwntk6545/dlwntk6545@112.216.253.125:1521:xe";
	private String insertSQL = "insert into EX01SUNGJUK values(exam.nextval, ?, ?, ?, ?, ?, ?, ?)";
	private String selectSQL = "select * from EX01SungJuk order by sjno desc";
	
	@Override
	public boolean insertSungJuk(EX01_SungJukVO s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;

		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setString(1, s.getName());
			pstmt.setInt(2, s.getKor());
			pstmt.setInt(3, s.getEng());
			pstmt.setInt(4, s.getMat());
			pstmt.setInt(5, s.getTot());
			pstmt.setDouble(6, s.getAvg());
			pstmt.setString(7, s.getGrd());
			
			pstmt.executeUpdate();
			result = true;
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDatabase(conn, pstmt, null);
		}
		return result;

	} // insertSungJuk 괄호

	private Connection openDatabase() {

		try {
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		return null;

	}

	private void closeDatabase(Connection c, PreparedStatement p, ResultSet r) {
		if (r != null) {try {r.close(); r = null;} catch (Exception e) {}}
		if (p != null) {try {p.close();p = null;} catch (Exception e) {}}
		if (c != null) {try {c.close();c = null;} catch (Exception e) {}}
	}

	@Override
	public List<EX01_SungJukVO> selectSungJuk() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<EX01_SungJukVO> result = new ArrayList<>();
		
		try{
			conn = openDatabase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				EX01_SungJukVO s = new EX01_SungJukVO();
				s.setKor(rs.getInt("kor"));
				s.setEng(rs.getInt("eng"));
				s.setMat(rs.getInt("mat"));
				
				result.add(s);
				
			}
			
		}catch (Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn, pstmt, rs);
		}
		
		return result;
	}
}
